/*
	For the FG and Class Store  process we are cosidering the Vendor Lot Number as 
	Lot Number

'
	Usage : exec sp_create_update_data_entry_inventory_lot 2672, 11, 'AA-CH-LLL', 'VenLot-TTT.1.0', 30000, null, null,null, '01/31/2007', null, 'ChipBond',null,'PROD',null, 'Running', 'Class STORE', null
*/
Alter proc sp_create_update_data_entry_inventory_lot (
	
	@PROCESSSTEPID		INT,
	@LOCATIONID			INT,	
	@FAB_LOT_NUMBER 	VARCHAR(50),
	@VENDOR_LOT_NUMBER 	VARCHAR(50),
	@Wafer_QTY_IN 		INT, 
	@WAFER_QTY_OUT 		INT, 
	@QTY_IN 			INT, 
	@QTY_OUT 			INT,
	@DATE_IN 			varchar(50),
	@DATE_OUT 			VARCHAR(50),
	@VENDOR_NAME 		VARCHAR(50),
	@WAFER_ID 			VARCHAR(100),
	@LOT_CATEGORY 		VARCHAR(10),	
	@LOT_DATE_CODE 		VARCHAR(50),
	@LOT_STATUS 		VARCHAR(50),
	@PROCESS_TYPE		VARCHAR(50),
	@LOT_COMMENTS 		VARCHAR(500),
	@ASSEMBLY_VENDOR	VARCHAR(50),
	@USER_NAME			VARCHAR(50),
	@PREV_SHIP_LOTID	INT,
	@PREV_PROCESS_LOTID INT ) 
as
begin
	DECLARE @LOT_ID 		INT
	DECLARE @VENDOR_ID 		INT
	DECLARE @PURCHASE_ORDER_ID 	INT
	DECLARE @THIS_LOT_STATUS 	VARCHAR(50)
	DECLARE @LOT_NUMBER		VARCHAR(50)
		
	DECLARE @DATE_TIME 	DATETIME
	DECLARE @START_DATE 	DATETIME
	DECLARE @END_DATE 	DATETIME
	
	/*IF @PROCESS_TYPE = '' OR @PROCESS_TYPE = 'FINISHED GOODS' or  @PROCESS_TYPE = 'CLASS STORE' OR @PROCESS_TYPE = 'MARK & PACK' OR @PROCESS_TYPE = 'TAPE & REEL'
	BEGIN
		set @LOT_NUMBER = @VENDOR_LOT_NUMBER
	END
	ELSE
	BEGIN
		set @LOT_NUMBER = @FAB_LOT_NUMBER
	END */
	set @LOT_NUMBER = ISNULL(@VENDOR_LOT_NUMBER, @FAB_LOT_NUMBER)

	select @this_lot_status = Lot_Current_Status, @lot_id = Lot_ID from Lot_Table where ProcessStep_ID = @processStepId and Location_ID = @locationId and Lot_Number = @LOT_NUMBER
	
	if @lot_id is null -- Check if the lot exists
	begin -- Create Lot
		
		insert into Lot_Table (ProcessStep_ID, Location_ID, Lot_Number, Fab_Lot_Number, Vendor_Lot_Number, Wafer_Qty_In, Wafer_Qty_Out, qty_in, Qty_Out, Date_In, Date_Out,Lot_Current_Status, Timestamp,Lot_Entered_By, Lot_Category, Lot_Date_Code, Lot_Comments, LOT_DROP_OUT, USER_NAME) 
			values (@processStepId, @locationId, @LOT_NUMBER, @FAB_LOT_NUMBER, @VENDOR_LOT_NUMBER, @Wafer_Qty_In, @Wafer_Qty_Out, @qty_in, @qty_out, @date_in, @date_out, @lot_status, CURRENT_TIMESTAMP, 0, @lot_category, @Lot_Date_Code, @lot_Comments, 0 , @USER_NAME )
		select @lot_id = @@IDENTITY
		--select * from Inventory_Lot
		insert into Inventory_Lot( Lot_ID, Wafer_id, Timestamp, ASSEMBLY_VENDOR, IL_Date_In, IL_Date_Out, IL_Qty_In, IL_Qty_Out, IL_Qty_Balance ) values (@lot_id, @WAFER_ID, CURRENT_TIMESTAMP, @ASSEMBLY_VENDOR, @date_in, @date_out, @qty_in, @qty_out, @qty_in )		
		
		IF @PROCESS_TYPE = 'FINISHED GOODS'
		BEGIN
			INSERT INTO FG_Data_Entry_Lot_Table( Lot_Id, Qty_In, Date_In, User_Name, Is_Modified, Timestamp )
				values ( @lot_id, @qty_in, @date_in, @USER_NAME, 1, CURRENT_TIMESTAMP)
		END
	end
	else
	begin 

		declare @temp_varchar varchar(20)
		set @temp_varchar = convert(varchar, current_timestamp, 101)+' 12:00AM'
		set @start_date = convert(datetime, @temp_varchar)
		set @temp_varchar = null
		set @temp_varchar = convert(varchar, current_timestamp, 101)+' 11:59PM' 
		set @end_date = convert(datetime, @temp_varchar)	
	
		/* Updating into lot History Table
		   Started Here*/
		select @date_time = (Lot_Table_History.timestamp) from Lot_Table_History where Lot_ID = @lot_id
		
			if @date_time between @start_date and @end_date
			begin
				update Lot_Table_History set Lot_Current_Status = @LOT_STATUS, Wafer_Qty_In = @Wafer_Qty_In, Wafer_Qty_Out = @Wafer_Qty_Out, Qty_In = @qty_in, Qty_Out = @Qty_Out, Date_In = @date_in, Date_Out = @Date_Out, Lot_Comments = @Lot_Comments, Lot_Date_Code = @Lot_Date_Code, Lot_Category = @Lot_category, LOT_DROP_OUT = 0 , User_Name = @USER_NAME Where Lot_ID = @lot_id
			end
			else
			begin	
				insert into Lot_Table_History(Lot_ID, Processstep_Id, Location_Id,Lot_Number, Fab_Lot_Number, Vendor_Lot_Number, Lot_Entered_By, Lot_Manual_Hold, Lot_Category, Wafer_Qty_In, Wafer_Qty_Out, Qty_In, Qty_Out, Date_In,Date_Out,Stage_Name,Stage_Position,Lot_Current_Status,Lot_Comments,Lot_Date_Code,Timestamp, LOT_DROP_OUT, USER_NAME )
	 			(select Lot_ID, Processstep_Id, Location_Id, Lot_Number, Fab_Lot_Number, Vendor_Lot_Number, Lot_Entered_By, Lot_Manual_Hold, Lot_Category, Wafer_Qty_In, Wafer_Qty_Out, Qty_In, Qty_Out, Date_In, Date_Out, Stage_Name, Stage_Position, Lot_Current_Status, Lot_Comments, Lot_Date_Code, CURRENT_TIMESTAMP, 0 , USER_NAME from Lot_Table where Lot_id = @lot_id)
			end
		/*Ended Here*/
		
	
		IF @PROCESS_TYPE = 'FINISHED GOODS'
		BEGIN

			declare @previous_qty_in int
			declare @increase_qty int
	
			SELECT @PREVIOUS_QTY_IN = QTY_IN FROM LOT_TABLE WHERE LOT_ID = @LOT_ID
	
			SET @INCREASE_QTY = @QTY_IN - @PREVIOUS_QTY_IN
	
			UPDATE LOT_TABLE SET LOT_CURRENT_STATUS = @LOT_STATUS, WAFER_QTY_IN = @WAFER_QTY_IN, WAFER_QTY_OUT = @WAFER_QTY_OUT, QTY_IN = @QTY_IN, QTY_OUT = @QTY_OUT, DATE_IN = @DATE_IN, DATE_OUT = @DATE_OUT, LOT_COMMENTS = @LOT_COMMENTS, LOT_DATE_CODE = @LOT_DATE_CODE, LOT_CATEGORY = @LOT_CATEGORY, LOT_DROP_OUT = 0, User_Name = @USER_NAME WHERE LOT_ID = @LOT_ID

			UPDATE INVENTORY_LOT SET TIMESTAMP = CURRENT_TIMESTAMP, WAFER_ID = @WAFER_ID, ASSEMBLY_VENDOR = @ASSEMBLY_VENDOR, IL_QTY_IN = @QTY_IN, IL_QTY_BALANCE = @QTY_IN  WHERE LOT_ID = @LOT_ID


			INSERT INTO FG_Data_Entry_Lot_Table( Lot_Id, Qty_In, Date_In, User_Name, Is_Modified, Timestamp )
			values ( @lot_id, @qty_in, @date_in, @USER_NAME, 0, CURRENT_TIMESTAMP)
		END
		ELSE
		BEGIN
			
			UPDATE LOT_TABLE SET LOT_CURRENT_STATUS = @LOT_STATUS, WAFER_QTY_IN = @WAFER_QTY_IN, WAFER_QTY_OUT = @WAFER_QTY_OUT, QTY_IN = @QTY_IN, QTY_OUT = @QTY_OUT, DATE_IN = @DATE_IN, DATE_OUT = @DATE_OUT, LOT_COMMENTS = @LOT_COMMENTS, LOT_DATE_CODE = @LOT_DATE_CODE, LOT_CATEGORY = @LOT_CATEGORY, LOT_DROP_OUT = 0, User_Name = @USER_NAME WHERE LOT_ID = @LOT_ID
			UPDATE INVENTORY_LOT SET TIMESTAMP = CURRENT_TIMESTAMP, WAFER_ID = @WAFER_ID, ASSEMBLY_VENDOR = @ASSEMBLY_VENDOR WHERE LOT_ID = @LOT_ID

		END
	END
	--Inserting into History table when lot is having dateout and Quantity Out		
	if ( @QTY_OUT IS NOT NULL AND @DATE_OUT IS NOT NULL) or ( @Wafer_Qty_Out IS NOT NULL AND @DATE_OUT IS NOT NULL )
	begin
		
		update Lot_Table set Lot_Current_Status = 'Close', Wafer_Qty_In = @Wafer_Qty_In, Wafer_Qty_Out = @Wafer_Qty_Out, Qty_In = @qty_in, Qty_Out = @Qty_Out, Date_In = @date_in, Date_Out = @Date_Out, Lot_Comments = @Lot_Comments, Lot_Date_Code = @Lot_Date_Code, Lot_Category = @Lot_category, LOT_DROP_OUT = 0, USER_NAME = @USER_NAME where Lot_ID = @lot_id
	
		insert into Lot_Table_History (Lot_Id, ProcessStep_ID, Location_ID, Lot_Number, Fab_Lot_Number, Vendor_Lot_Number,Wafer_Qty_In, Wafer_Qty_Out, Qty_In, Qty_Out, Date_In, Date_Out,Lot_Current_Status, Timestamp, Lot_Entered_By, Lot_Category, Lot_Date_Code, LOT_DROP_OUT, USER_NAME ) 
		values ( @lot_id, @processStepId, @locationId, @VENDOR_LOT_NUMBER, @FAB_LOT_NUMBER, @VENDOR_LOT_NUMBER, @Wafer_Qty_In, @Wafer_Qty_Out, @qty_in, @qty_out, @date_in, @date_out, 'Close', CURRENT_TIMESTAMP, 0, @lot_category, @Lot_Date_Code, 0, @USER_NAME )
	
	end

	IF @PREV_SHIP_LOTID > 0 OR @PREV_SHIP_LOTID = -1
	BEGIN
			UPDATE SHIPPED_LOT SET STATUS = 'Close', Ship_Status = 1 , Close_Date = CURRENT_TIMESTAMP WHERE SHIPPEDLOT_ID = @PREV_SHIP_LOTID
	END
	ELSE
	BEGIN
		IF @PROCESS_TYPE IN ('FINISHED GOODS', 'CLASS STORE')
		BEGIN		
			/* Closing Transit Lots and Previous Lots*/
			Exec SP_CLOSE_PREVIOUSPROCESS_AND_TRANSITLOTS @LOT_NUMBER, @FAB_LOT_NUMBER, @QTY_IN, @DATE_IN 
		END
		ELSE
		BEGIN
			
			/* Closing Transit Lots and Previous Lots*/
			Exec SP_CLOSE_PREVIOUSPROCESS_AND_TRANSITLOTS @LOT_NUMBER, @FAB_LOT_NUMBER, @WAFER_QTY_IN , @DATE_IN 
		END
	END
end
